﻿/*
 * @author Lee 
 * Time: 20180403 
 * Abstract: DateTable的一些操作， 之前做项目中用到的，与SharePoint的无关，觉得不错，所以放到这里面来。
 */

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace SharePointHelper.DLL.SSOM
{
	public class DataTable
	{
		public class Column
		{
			public string DisplayName { get; set; }
			public string Name { get; set; }
			public Column(string name)
			{
				Name = name;
				DisplayName = name;
			}
			public Column(string name, string displayName)
			{
				if (string.IsNullOrEmpty(displayName) && !string.IsNullOrEmpty(name))
				{
					displayName = name;
				}
				else if (string.IsNullOrEmpty(name) && !string.IsNullOrEmpty(displayName))
				{
					name = displayName;
				}
				Name = name;
				DisplayName = displayName;
			}
		}

		/// <summary>
		/// get column value for row passed
		/// </summary>
		/// <param name="r"></param>
		/// <param name="columnName"></param>
		/// <returns></returns>
		public static string GetValue(DataRow r, string columnName, bool isHtmlEncode)
		{
			string columnValue = null;
			if (r[columnName] != null && r[columnName] != DBNull.Value)
			{
				if (r[columnName] is DateTime) { columnValue = ((DateTime)r[columnName]).ToShortDateString(); }
				else { columnValue = r[columnName].ToString(); }
				if (isHtmlEncode) { columnValue = System.Web.HttpUtility.HtmlEncode(columnValue); }
			}
			return columnValue;
		}

		public static string ConvertDataTableToHtmlTable(System.Data.DataTable dt, string[] columns,
			bool isInsertTableTags, bool isInsertHeaderRow)
		{
			// convert to columns
			List<Column> columnsList = new List<Column>();
			if (columns != null)
			{
				foreach (string c in columns) { columnsList.Add(new Column(c)); }
			}
			return ConvertDataTableToHtmlTable(dt, columnsList, isInsertTableTags, isInsertHeaderRow);
		}

		public static string ConvertDataTableToHtmlTable(System.Data.DataTable dt, List<Column> columns, 
			bool isInsertTableTags, bool isInsertHeaderRow)
		{
			string o = null;
			try
			{
				// init
				if (dt == null) { throw new Exception("DataTable passed is null"); }
				if (columns == null) { columns = new List<Column>(); }
				if (columns.Count == 0)
				{
					foreach (DataColumn c in dt.Columns) { columns.Add(new Column(c.ColumnName)); }
				}


				if (isInsertHeaderRow)
				{
					o += "<tr>";
					foreach (Column c in columns)
					{
						o += "<td align='center' valign='bottom' style='background-color:#ccc;'><b>" 
							+ System.Web.HttpUtility.HtmlEncode(c.DisplayName) + "</b></td>";
					}
					o += "</tr>";
				}
				foreach (DataRow r in dt.Rows)
				{
					o += "\n<tr>";
					foreach (Column c in columns)
					{
						
						o += "<td align='left' valign='top'>" + GetValue(r, c.Name, true) + "</td>";
					}
					o += "</tr>";
				}

				if (isInsertTableTags)
				{
					o = "<table border='1' cellpadding='4' cellspacing='0' style='border-collapse:collapse;'>"
						+ o + "</table>";
				}
			}
			catch (Exception ex)
			{
				Log.WriteError("sdhtytkdsjrjsrj75srt", ex);
				o = null;
			}
			return o;
		}


		/// <summary>
		/// Get datatable using passed sql query
		/// </summary>
		/// <param name="sql">sql query, parameterized</param>
		/// <param name="connectionString"></param>
		/// <param name="parameters">sql parameters to match what's used in the query</param>
		/// <returns></returns>
		public static System.Data.DataTable GetDataTable(string sql, List<SqlParameter> parameters, string connectionString)
		{
			var dt = new System.Data.DataTable();
			try
			{
				// init
				if (string.IsNullOrEmpty(sql)) { throw new Exception("SQL Query passed is null"); }
				if (string.IsNullOrEmpty(connectionString)) { throw new Exception("connection string passed is null"); }


				using (SqlConnection connection = new SqlConnection(connectionString))
				{
					// create connection
					SqlCommand cmd = new SqlCommand(sql, connection);
					if (cmd.Connection == null) { throw new Exception("Failed to open connection"); }
					
					// parameters
					if (parameters != null && parameters.Count > 0)
					{
						foreach (var p in parameters) { cmd.Parameters.Add(p); }
					}

					// open connection
					cmd.Connection.Open();

					// convert results into datatable
					using (SqlDataReader r = cmd.ExecuteReader()) { dt.Load(r); }
				}
			}
			catch (Exception ex)
			{
				var parametersAsString = string.Empty; if (parameters != null) { foreach (var p in parameters) { parametersAsString += p.ParameterName + ": " + p.Value + ", "; } }
				Log.WriteError("ery7iyuydGGrsdsrseresraaewt", ex, 
					"query: " + sql + "\r\nparameters: " + parametersAsString);
				dt = null;
			}
			return dt;
		}

		/// <summary>
		/// Get DataTable from passed sql query
		/// </summary>
		/// <param name="sql">query, no parameters</param>
		/// <param name="connectionString"></param>
		/// <returns></returns>
		public static System.Data.DataTable GetDataTable(string sql, string connectionString)
		{
			// call original
			return GetDataTable(sql, null, connectionString);
		}
	}
}
